How to get zero-downtime scaling from single-region to multi-region applications

CockroachDB offers a number of features aimed at making it easy to support your application in multiple regions. In CockroachDB 20.1, we introduced a new feature Online Primary Key Changes that allows you to upgrade your application from a single-region to multi-region with zero downtime. We wrote a bit recently about the technical challenges involved in building online primary key changes and in this blog post, we'll walk through some of the use cases and benefits the feature can lead to. 

A Quick Example

First, download CockroachDB 20.1 using your preferred method including Linux, Mac, or Windows binaries, Docker images, or building directly from source.

This example will focus on the fictional ride-sharing company MovR that we’ve previously written about when introducing lateral joins. MovR is a ride sharing company that operates in multiple cities around the globe. We have explored its schema in great detail in this multi-region blog post. You can also use the new CockroachDB demo feature that we recently blogged about to try this out in less than five minutes.

To use CockroachDB demo (and the more complex rides table with pre-populated data) enter the following command:

./cockroach demo

This command will automatically load the MovR tables and data:

show tables;

table_name
+----------------------------+
  promo_codes
  rides
  user_promo_codes
  users
  vehicle_location_histories
  vehicles
(6 rows)

You can see that this rides table has already been well set up for multi-region as it has a compound primary key that places the region in front of id:

show create table rides;
  table_name |                                                        create_statement
-------------+----------------------------------------------------------------------------------------------------------------------------------
  rides      | CREATE TABLE rides (
             |     id UUID NOT NULL,
             |     city VARCHAR NOT NULL,
             |     vehicle_city VARCHAR NULL,
             |     rider_id UUID NULL,
             |     vehicle_id UUID NULL,
             |     start_address VARCHAR NULL,
             |     end_address VARCHAR NULL,
             |     start_time TIMESTAMP NULL,
             |     end_time TIMESTAMP NULL,
             |     revenue DECIMAL(10,2) NULL,
             |     CONSTRAINT "primary" PRIMARY KEY (city ASC, id ASC),
             |     CONSTRAINT fk_city_ref_users FOREIGN KEY (city, rider_id) REFERENCES users(city, id),
             |     CONSTRAINT fk_vehicle_city_ref_vehicles FOREIGN KEY (vehicle_city, vehicle_id) REFERENCES vehicles(city, id),
             |     INDEX rides_auto_index_fk_city_ref_users (city ASC, rider_id ASC),
             |     INDEX rides_auto_index_fk_vehicle_city_ref_vehicles (vehicle_city ASC, vehicle_id ASC),
             |     FAMILY "primary" (id, city, vehicle_city, rider_id, vehicle_id, start_address, end_address, start_time, end_time, revenue),
             |     CONSTRAINT check_vehicle_city_city CHECK (vehicle_city = city)
             | )
(1 row)

Since we want to show the journey of going from single-region to multi-region, I’ve modified the rides table to remove city from the primary key (note: you could also do this with ALTER TABLE rides ALTER PRIMARY KEY USING COLUMNS (id ASC); if you want to use the existing MovR rides table). To follow along at home, we will create and use a new database and then create a new rides table as follows:

CREATE DATABASE movr_blog;

USE movr_blog;

CREATE TABLE rides (
	id
		UUID NOT NULL,
	city
		VARCHAR NOT NULL,
	vehicle_city
		VARCHAR NULL,
	rider_id
		UUID NULL,
	vehicle_id
		UUID NULL,
	start_address
		VARCHAR NULL,
	end_address
		VARCHAR NULL,
	start_time
		TIMESTAMP NULL,
	end_time
		TIMESTAMP NULL,
	revenue
		DECIMAL(10,2) NULL
);

Now, we want to change this primary key so we need to use the following alter table statement:

ALTER TABLE rides ALTER PRIMARY KEY USING COLUMNS (city ASC, id ASC);

NOTICE: primary key changes are finalized asynchronously; further schema changes on this table may be restricted until the job completes
ALTER TABLE

Time: 189.061ms

Now in this example, we don’t have a load pointed at the cluster or a large amount of data to change so it is a relatively fast operation. As we will see below, online schema changes are designed to have minimal impact on foreground activity by not locking the table out for use by your customers. 

A bit more involved MovR example

Let’s show a more involved example that considers the entire MovR database. We make the entire MovR workload available in this GitHub repository.

To set up this example, make sure that you have docker installed and running.

brew install docker

You'll also need to install CockroachDB 20.1.

brew install cockroachdb

Then, start a cockroachdb node on your laptop:

./cockroach start-single-node --insecure --host localhost --background

Next, create the MovR database:

cockroach sql --insecure --host localhost -e "create database movr;"

Now, let’s generate some fakedata for the MovR database:

docker run -it --rm cockroachdb/movr:movr-20.1-beta.20.4.1 --url "postgres://root@docker.for.mac.localhost:26257/movr?sslmode=disable" load --num-users 100 --num-rides 100 --num-vehicles 10

We can open the webui and see what’s going on by visiting the localhost directly in the browser:

http://localhost:8080/#/overview/list

We can even see what the existing tables look like, for example, the rides table:

http://localhost:8080/#/database/movr/table/rides

Now let’s point load at it:

docker run -it --rm cockroachdb/movr:movr-20.1-beta.20.4.2 --app-name "movr-loadgen" --url "postgres://root@docker.for.mac.localhost:26257/movr?sslmode=disable" run --multi-region

We can also see this load in the webui in the metrics tab:

http://localhost:8080/#/metrics/overview/cluster

Now, let’s upgrade this to a multi-region cluster. Normally, if you were doing this by hand, you’d need to run the following commands see through Docker:

docker run -it --rm cockroachdb/movr:movr-20.1-beta.20.4.1 --app-name "movr-loadgen" --url "postgres://root@docker.for.mac.localhost:26257/movr?sslmode=disable" configure-multi-region --preview-queries

[INFO] (MainThread) connected to movr database @ postgres://root@docker.for.mac.localhost:26257/movr?sslmode=disable
DDL to convert a single region database to multi-region
===primary key alters===
ALTER TABLE users ALTER PRIMARY KEY USING COLUMNS (city, id);
ALTER TABLE rides ALTER PRIMARY KEY USING COLUMNS (city, id);
ALTER TABLE vehicle_location_histories ALTER PRIMARY KEY USING COLUMNS (city, ride_id, timestamp);
ALTER TABLE vehicles ALTER PRIMARY KEY USING COLUMNS (city, id);
ALTER TABLE user_promo_codes ALTER PRIMARY KEY USING COLUMNS (city, user_id, code);
===foreign key alters===
DROP INDEX users_city_idx;
ALTER TABLE vehicles DROP CONSTRAINT fk_owner_id_ref_users;
CREATE INDEX ON vehicles (city, owner_id);
DROP INDEX vehicles_auto_index_fk_owner_id_ref_users;
DROP INDEX vehicles_city_idx;
ALTER TABLE vehicles ADD CONSTRAINT fk_owner_id_ref_users_mr FOREIGN KEY (city, owner_id) REFERENCES users (city,id);
ALTER TABLE rides DROP CONSTRAINT fk_rider_id_ref_users;
CREATE INDEX ON rides (city, rider_id);
ALTER TABLE rides ADD CONSTRAINT fk_rider_id_ref_users_mr FOREIGN KEY (city, rider_id) REFERENCES users (city,id);
ALTER TABLE rides DROP CONSTRAINT fk_vehicle_id_ref_vehicles;
CREATE INDEX ON rides (city, vehicle_id);
ALTER TABLE rides ADD CONSTRAINT fk_vehicle_id_ref_vehicles_mr FOREIGN KEY (city, vehicle_id) REFERENCES vehicles (city,id);
DROP INDEX rides_auto_index_fk_rider_id_ref_users;
DROP INDEX rides_auto_index_fk_vehicle_id_ref_vehicles;
ALTER TABLE user_promo_codes DROP CONSTRAINT fk_user_id_ref_users;
ALTER TABLE user_promo_codes ADD CONSTRAINT fk_user_id_ref_users_mr FOREIGN KEY (city, user_id) REFERENCES users (city,id);

We’ve scripted it to complete these commands for you so you can remove the --preview-queries to see this execute:

docker run -it --rm cockroachdb/movr:movr-20.1-beta.20.4.1 --app-name "movr-loadgen" --url "postgres://root@docker.for.mac.localhost:26257/movr?sslmode=disable" configure-multi-region

You can see these run in the jobs section of the webui or by running show jobs

http://localhost:8080/#/jobs

We can also verify that this occurred in the webui or by running show create table rides;

How did this affect the cluster? 

Back in the metrics table we can see that p99 latency rose only slightly while the schema changes took effect but we had no dropoff in throughput:

You can increase the cluster size, increase the workload, and otherwise try out CockroachDB using MovR!

Future-proof your application

Looking for other ways CockroachDB makes multi-region deployments easy? We previously covered How to Leverage Geo-partitioning, Reducing Multi-Region Latency with Follower Reads, and most recently How to Build a Multi-Region Application on CockroachDB. 

CockroachDB provides developers with powerful tools to create multi-region applications. But your application doesn’t need to be designed for multi-region from inception, we also provide you the tools to grow your application from single-region to multi-region. Try CockroachDB Dedicated for free for 30 days.